/**

This .do file picks up where Jeff left off. terror_jeff_RA_work.dta is the terror data I import to R to merge. I also format and save the presidential, house, and senate election data for use in R.


Important: To use, ctrl-h to find and replace "C:\Users\Sean\Dropbox\Eran\" with the location of your Dropbox directory.

**********************************************************************
*****************Set up command directory for files*******************
**********************************************************************

*/
	clear
	cd "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Data_do_files\Terror Data" 

**********************************************************************
*****************Begin analysis***************************************
**********************************************************************

*Import the base terror dataset

	import excel "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Data_do_files\Terror Data\GTD.xlsx", sheet("Eventid uniqid") cellrange(A4:EP2385) firstrow
	
	save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Data_do_files\Terror Data\terror_jeff_RA_work.dta", replace
	
	clear

*Merge in presidential election data - save pres and terror data in working directory for processing in R
	
		import excel "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\US_presidential_elections\presidential_data.xlsx", sheet("Sheet1") firstrow
		
			drop if county_name==""
			drop if LSAD_TRANS!="County"
			
			ren ST state_fips
			ren CTY county_fips
			
			destring state_fips, replace
			destring county_fips, replace
			
			ren Year year
						
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\US_presidential_elections\1972_to_2016_stata.dta", replace
		
		use "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Data_do_files\Terror Data\terror_jeff_RA_work.dta", clear
		
			gen county_fips=FIPSCounty-int(FIPSCounty/100)*100
			gen state_fips=int(FIPSCounty/1000)
			
			ren iyear year

			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Data_do_files\Terror Data\terror.dta", replace
			
* Change all this. Maybe make global paths
			
cd "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\"

		*1994
		clear
		insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_1994.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v13 v14  cd st cty fips
			gen year=1994
			drop if _n > 3254
			ren v14 v15
			ren v13 v14
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h1994.dta", replace

		*1996
		clear
		insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_1996.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=1996
			drop if _n > 3254
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h1996.dta", replace
		
		*1998
		clear
		insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_1998.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=1998
			drop if _n > 3254
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h1998.dta", replace
		
		*2000
		clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_2000.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=2000
			drop if _n > 3254
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h2000.dta", replace
			
		*2002
		clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_2002.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=2002
			drop if _n > 3255
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h2002.dta", replace
		
		*2004
		clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_2004.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=2004
			drop if _n > 3219
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h2004.dta", replace
		
		*2006
		clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_2006.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=2006
			drop if _n > 3255
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h2006.dta", replace
		
		*2008
			clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_2008.csv"
			*ren expandatleft v1
			keep expandatleft v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=2008
			drop if _n > 3254
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h2008.dta", replace
		
		*2010
			clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_2010.csv"
			ren expandatleft v1
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=2010
			drop if _n > 3256
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h2010.dta", replace
		
		*2012
			clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_2012.csv"
			ren expandatleft v1
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=2012
			drop if _n > 3256
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h2012.dta", replace
		
		*2014
			clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_2014.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=2014
			drop if _n > 3255
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h2014.dta", replace
		
		*2016
			clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_Election_Data_2016.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  cd st cty fips
			gen year=2016
			drop if _n > 3255
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\h2016.dta", replace
			
		clear
			use h1994
				append using h1996 h1998 h2000 h2002 h2004 h2006 h2008 h2010 h2012 h2014 h2016, force
				
				drop if d==.
				ren v1 county_name
				ren v2 state_abbrev
				ren st state_fips
				ren cty county_fips
				drop if state_abbrev=="T"
				
				replace v8 = subinstr(v8, "%", "",.)
				replace democratic = subinstr(democratic, "%", "",.)
				replace republican = subinstr(republican, "%", "",.)
				replace independent = subinstr(independent, "%", "",.)
				replace other = subinstr(other, "%", "",.)
				
				replace v8 = subinstr(v8, "-", "",.)
				replace democratic = subinstr(democratic, "-", "",.)
				replace republican = subinstr(republican, "-", "",.)
				replace independent = subinstr(independent, "-", "",.)
				replace other = subinstr(other, "-", "",.)
				
				replace v8 = subinstr(v8, ",", "",.)
				replace democratic = subinstr(democratic, ",", "",.)
				replace republican = subinstr(republican, ",", "",.)
				replace independent = subinstr(independent, ",", "",.)
				replace other = subinstr(other, ",", "",.)
				replace totalvote = subinstr(totalvote, ",", "",.)
				replace margin = subinstr(margin, ",", "",.)
				replace v14 = subinstr(v14, ",", "",.)
				replace v15 = subinstr(v15, ",", "",.)

				replace i = subinstr(i, "#N/A", "",.)
				
				destring totalvote v8 democratic republican independent other cd i margin , replace

			gen prev_leg_elec_year=year
				replace prev_leg_elec_year=1970 if year==1972 | year==1971
				replace prev_leg_elec_year=1972 if year==1973 | year==1974
				replace prev_leg_elec_year=1974 if year==1975 | year==1976
				replace prev_leg_elec_year=1976 if year==1977 | year==1978
				replace prev_leg_elec_year=1978 if year==1979 | year==1980
				replace prev_leg_elec_year=1980 if year==1981 | year==1982
				replace prev_leg_elec_year=1982 if year==1983 | year==1984
				replace prev_leg_elec_year=1984 if year==1985 | year==1986
				replace prev_leg_elec_year=1986 if year==1987 | year==1988
				replace prev_leg_elec_year=1988 if year==1989 | year==1990
				replace prev_leg_elec_year=1990 if year==1991 | year==1992
				replace prev_leg_elec_year=1992 if year==1993 | year==1994
				replace prev_leg_elec_year=1994 if year==1995 | year==1996
				replace prev_leg_elec_year=1996 if year==1997 | year==1998
				replace prev_leg_elec_year=1998 if year==2000 | year==2002
				replace prev_leg_elec_year=2000 if year==2002 | year==2003
				replace prev_leg_elec_year=2002 if year==2003 | year==2004
				replace prev_leg_elec_year=2004 if year==2005 | year==2006 
				replace prev_leg_elec_year=2006 if year==2007 | year==2008
				replace prev_leg_elec_year=2008 if year==2009 | year==2010
				replace prev_leg_elec_year=2010 if year==2011 | year==2012
				replace prev_leg_elec_year=2012 if year==2013 | year==2014
				replace prev_leg_elec_year=2014 if year==2015 | year==2016
				
			gen next_leg_elec_year=year
				replace next_leg_elec_year=1974 if year==1972 | year==1973
				replace next_leg_elec_year=1976 if year==1974 | year==1975
				replace next_leg_elec_year=1978 if year==1976 | year==1977
				replace next_leg_elec_year=1980 if year==1978 | year==1979
				replace next_leg_elec_year=1982 if year==1980 | year==1981
				replace next_leg_elec_year=1984 if year==1982 | year==1983
				replace next_leg_elec_year=1986 if year==1984 | year==1985
				replace next_leg_elec_year=1988 if year==1986 | year==1987
				replace next_leg_elec_year=1990 if year==1988 | year==1989
				replace next_leg_elec_year=1992 if year==1990 | year==1991
				replace next_leg_elec_year=1994 if year==1992 | year==1993
				replace next_leg_elec_year=1996 if year==1994 | year==1995
				replace next_leg_elec_year=1998 if year==1996 | year==1997
				replace next_leg_elec_year=2000 if year==1998 | year==1999
				replace next_leg_elec_year=2002 if year==2000 | year==2001
				replace next_leg_elec_year=2004 if year==2002 | year==2003
				replace next_leg_elec_year=2006 if year==2004 | year==2005
				replace next_leg_elec_year=2008 if year==2006 | year==2007
				replace next_leg_elec_year=2010 if year==2008 | year==2009
				replace next_leg_elec_year=2012 if year==2010 | year==2011
				replace next_leg_elec_year=2014 if year==2012 | year==2013
				replace next_leg_elec_year=2016 if year==2014 | year==2015
				
				ren d demo_elecvote_h
				ren r repub_elecvotes_h
				ren i ind_elecvotes_h
				ren v8 margin_pct_h
				ren democratic demo_pct_h
				ren republican repub_pct_h
				ren independent ind_pct_h
				ren other other_pct_h
				ren v14 dem
				ren v15 rep

				
				gen house_or_senate = "house"
					
		save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\House\House_merged_congress.dta", replace


	*Senate
	cd "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\"

		*1994
		clear
		insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_1994.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=1994
			drop if _n > 2026
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s1994.dta", replace

		*1996
		clear
		insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_1996.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=1996
			drop if _n > 2395
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s1996.dta", replace
		
		*1998
		clear
		insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_1998.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=1998
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s1998.dta", replace
		
		*2000
		clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_2000.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15   st cty fips
			gen year=2000
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s2000.dta", replace
			
		*2002
		clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_2002.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=2002
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s2002.dta", replace
		
		*2004
		clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_2004.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=2004
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s2004.dta", replace
		
		*2006
		clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_2006.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=2006
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s2006.dta", replace
		
		*2008
			clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_2008.csv"
			ren expandatleft v1
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=2008
			drop if _n > 2395
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s2008.dta", replace
		
		*2010
			clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_2010.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=2010
			drop if _n > 2230
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s2010.dta", replace
		
		*2012
			clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_2012.csv"
			ren expandatleft v1
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=2012
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s2012.dta", replace
		
		*2014
			clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_2014.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=2014
			drop if _n > 2403
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s2014.dta", replace
		
		*2016
			clear
			insheet using "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Sen_Election_Data_2016.csv"
			keep v1 v2 totalvote d r i margin v8 democratic republican independent other v14 v15  st cty fips
			gen year=2016
			save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\s2016.dta", replace
			
		clear
			use s1994
				append using s1996 s1998 s2000 s2002 s2004 s2006 s2008 s2010 s2012 s2014 s2016, force
				
				drop if d==.
				ren v1 county_name
				ren v2 state_abbrev
				ren st state_fips
				ren cty county_fips
				drop if state_abbrev=="T"
				
				replace v8 = subinstr(v8, "%", "",.)
				replace democratic = subinstr(democratic, "%", "",.)
				replace republican = subinstr(republican, "%", "",.)
				replace independent = subinstr(independent, "%", "",.)
				replace other = subinstr(other, "%", "",.)
				
				replace v8 = subinstr(v8, "-", "",.)
				replace democratic = subinstr(democratic, "-", "",.)
				replace republican = subinstr(republican, "-", "",.)
				replace independent = subinstr(independent, "-", "",.)
				replace other = subinstr(other, "-", "",.)
				
				replace v8 = subinstr(v8, ",", "",.)
				replace democratic = subinstr(democratic, ",", "",.)
				replace republican = subinstr(republican, ",", "",.)
				replace independent = subinstr(independent, ",", "",.)
				replace other = subinstr(other, ",", "",.)
				replace totalvote = subinstr(totalvote, ",", "",.)
				replace margin = subinstr(margin, ",", "",.)
				replace v14 = subinstr(v14, ",", "",.)
				replace v15 = subinstr(v15, ",", "",.)

				
				replace i = subinstr(i, "#N/A", "",.)
					replace i = subinstr(i, "#N/A", "",.)
					replace margin = subinstr(margin, "#NUM!", "",.)
					replace v8 = subinstr(v8, "#NUM!", "",.)
		
				destring totalvote v8 democratic republican independent other i margin , replace

			gen prev_leg_elec_year=year
				replace prev_leg_elec_year=1970 if year==1972 | year==1971
				replace prev_leg_elec_year=1972 if year==1973 | year==1974
				replace prev_leg_elec_year=1974 if year==1975 | year==1976
				replace prev_leg_elec_year=1976 if year==1977 | year==1978
				replace prev_leg_elec_year=1978 if year==1979 | year==1980
				replace prev_leg_elec_year=1980 if year==1981 | year==1982
				replace prev_leg_elec_year=1982 if year==1983 | year==1984
				replace prev_leg_elec_year=1984 if year==1985 | year==1986
				replace prev_leg_elec_year=1986 if year==1987 | year==1988
				replace prev_leg_elec_year=1988 if year==1989 | year==1990
				replace prev_leg_elec_year=1990 if year==1991 | year==1992
				replace prev_leg_elec_year=1992 if year==1993 | year==1994
				replace prev_leg_elec_year=1994 if year==1995 | year==1996
				replace prev_leg_elec_year=1996 if year==1997 | year==1998
				replace prev_leg_elec_year=1998 if year==2000 | year==2002
				replace prev_leg_elec_year=2000 if year==2002 | year==2003
				replace prev_leg_elec_year=2002 if year==2003 | year==2004
				replace prev_leg_elec_year=2004 if year==2005 | year==2006 
				replace prev_leg_elec_year=2006 if year==2007 | year==2008
				replace prev_leg_elec_year=2008 if year==2009 | year==2010
				replace prev_leg_elec_year=2010 if year==2011 | year==2012
				replace prev_leg_elec_year=2012 if year==2013 | year==2014
				replace prev_leg_elec_year=2014 if year==2015 | year==2016
				
			gen next_leg_elec_year=year
				replace next_leg_elec_year=1974 if year==1972 | year==1973
				replace next_leg_elec_year=1976 if year==1974 | year==1975
				replace next_leg_elec_year=1978 if year==1976 | year==1977
				replace next_leg_elec_year=1980 if year==1978 | year==1979
				replace next_leg_elec_year=1982 if year==1980 | year==1981
				replace next_leg_elec_year=1984 if year==1982 | year==1983
				replace next_leg_elec_year=1986 if year==1984 | year==1985
				replace next_leg_elec_year=1988 if year==1986 | year==1987
				replace next_leg_elec_year=1990 if year==1988 | year==1989
				replace next_leg_elec_year=1992 if year==1990 | year==1991
				replace next_leg_elec_year=1994 if year==1992 | year==1993
				replace next_leg_elec_year=1996 if year==1994 | year==1995
				replace next_leg_elec_year=1998 if year==1996 | year==1997
				replace next_leg_elec_year=2000 if year==1998 | year==1999
				replace next_leg_elec_year=2002 if year==2000 | year==2001
				replace next_leg_elec_year=2004 if year==2002 | year==2003
				replace next_leg_elec_year=2006 if year==2004 | year==2005
				replace next_leg_elec_year=2008 if year==2006 | year==2007
				replace next_leg_elec_year=2010 if year==2008 | year==2009
				replace next_leg_elec_year=2012 if year==2010 | year==2011
				replace next_leg_elec_year=2014 if year==2012 | year==2013
				replace next_leg_elec_year=2016 if year==2014 | year==2015
				
				ren d demo_elecvote_s
				ren r repub_elecvotes_s
				ren i ind_elecvotes_s
				ren v8 margin_pct_s
				ren democratic demo_pct_s
				ren republican repub_pct_s
				ren independent ind_pct_s
				ren other other_pct_s
				ren v14 dem
				ren v15 rep

				
				gen house_or_senate = "senate"
								
		save "C:\Users\Sean\Dropbox\Eran\Terrorism_Left\Election_datasets\Congressional Elections\Senate\Senate_merged_congress.dta", replace
